import pymysql
import xlrd
def user_info(sql,param):
    con=pymysql.connect(host="losthost",port=3306,user="root",password=123456,db="user_info")
    sheet=con.sheet_by_index(0)
    cursor=con.cursor()
    con.execute('create table user_info(id int(11) ,idcard varchar(50) ,username varchar(50) ,realname varchar(50) ,pwd varchar(50) ,telphone varchar(12) ,email varchar(100) ,age int(11) ,sex varchar(20) ,address varchar(200) ,hiredate date ,sal double(9,2) ,job varchar(100) ,company varchar(100) ')
    sql='insert into user_info(id,idcard,username,realname,pwd,telphone,email,age,sex,address,hiredate,sal,job,conmany)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
    for i in range(1,sheet.nrows):
        id=int(sheet.cell_value(i,0))
        idcard=str(sheet.cell_value(i,1)).strip()
        username=str(sheet.cell_value(i,2)).strip()
        realname=str(sheet.cell_value(i,3)).strip()
        pwd=str(sheet.cell_value(i,4)).strip()
        telphone=str(sheet.cell_value(i,5)).strip()
        email=str(sheet.cell_value(i,6)).strip()
        age=int(sheet.cell_value(i,7))
        sex=str(sheet.cell_value(i,8)).strip()
        address = str(sheet.cell_value(i, 9)).strip()
        hiredate= sheet.cell_value(i, 10)
        sal = float(sheet.cell_value(i, 11))
        job = str(sheet.cell_value(i, 12)).strip()
        company = str(sheet.cell_value(i, 13)).strip()
    cursor.executemany(sql,param)
    con.commit()
    con.close()
